-- This demo scripts shows some things around code pages and how they work with the varchar data type.
-- © Erland Sommarskog 2024.
USE master
go
-- Find what is your server collation and the code page of the same.
SELECT serverproperty('Collation') AS Collation,
       collationproperty(convert(sysname, serverproperty('Collation')), 
                        'CodePage') AS [Code page]
go
-- Compare how these strings appear as nvarchar (N in front) and varchar (no N). 
-- The exact outcome depends on the code page of your server collation, but at 
-- most one string will appear unmutilated with varchar. The words are German
-- for "extra large size", the name for a popular Balkan dish and Naka-Meguro, 
-- a district in Tokyo.
SELECT N'Übergröße', N'ćevapčići', N'中目黒'
SELECT  'Übergröße',  'ćevapčići',  '中目黒'
go
-- Create a database with a Japanese collation and move there.
CREATE DATABASE Japanese_CI_AS COLLATE Japanese_CI_AS
go
USE Japanese_CI_AS
go
-- Which is the current code page for varchar?
SELECT collationproperty('Japanese_CI_AS', 'CodePage')
go
-- Naka-Meguro now appears correctly with varchar, the other two do not.
SELECT N'Übergröße', N'ćevapčići', N'中目黒'
SELECT  'Übergröße',  'ćevapčići',  '中目黒'
go
-- Create a table and insert Naka-Meguro (in Kanji) and Yokohama (in Latin.)
-- len() return the number of characters (excl. trailing blanks), datalength
-- returns the number of bytes. Study the result set!
CREATE TABLE words (word varchar(8) NOT NULL)
INSERT words(word)
   VALUES('中目黒'), ('Yokohama')
SELECT word, len(word) AS charlen, datalength(word) AS bytelen
FROM   words
go
-- Try to add this railway station in the Tokyo region to the table. 
-- It is five characters so it should fit in varchar(8) - or no?
INSERT words(word)
  VALUES('二子玉川駅')
go
